package nz.ac.massey.webtech.servlets.db;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

/**
 * Servlet that displayes a SQL table.
 * @author  Jens Dietrich   
 * @version 1.0
 */
public class SQLQueryServlet extends HttpServlet {
    
    /** Initializes the servlet.
     */
    public void init(ServletConfig config) throws ServletException {
        super.init(config);
        
    }
    
    /** Destroys the servlet.
     */
    public void destroy() {
        
    }
    
    /** Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
       
        out.println("<html>");
        out.println("<head>");
        out.println("<title>SQL Query Servlet</title>");
        out.println("</head>");
        out.println("<body>");
         
        out.println("<h1>SQL Query Servlet</h1>");
        
        String driver = request.getParameter("driver");
        String url = request.getParameter("url");
        String query = request.getParameter("query");
        
        // load driver and establish connection
        Connection connection = null;
        try {
            Class.forName(driver);
        }
        catch (ClassNotFoundException x) {
            handleException("Driver not found: "+driver,x,out);
        }
        try {
            connection = DriverManager.getConnection(url);
        }
        catch (SQLException x) {
            handleException("Cannot connect to database: " + url,x,out);
        }
        // execute query
        try {
            Statement stmnt = connection.createStatement();
            ResultSet rs = stmnt.executeQuery(query);
            ResultSetMetaData metaData = rs.getMetaData();
            int colCount = metaData.getColumnCount();
            // start displaying a table
            out.println("<table border>");
            // the header
            out.print("<tr>");
            for (int i=0;i<colCount;i++) {
                out.print("<th>");
                out.print(metaData.getColumnName(i+1));
                out.print("</th>");
            }
            out.println("</tr>");
            // print data rows
            out.print("<tr>");
            while (rs.next()) {
                out.print("<tr>");
                for (int i=0;i<colCount;i++) {
                    out.print("<td>");
                    out.print(rs.getObject(i+1));
                    out.print("</td>");
                }
                out.println("</tr>");  
            }
        }
        catch (Exception x) {
            handleException("Error executing query: " + url,x,out);
        }
        // important: close the connection
        finally {
            try {
                if (connection!=null) connection.close();                
            }
            catch (SQLException x) {
                handleException("Cannot close connection",x,out);
            }
        }
        out.println("</body>");
        out.println("</html>");
        
        
        out.close();
    }
    
    /** Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }
    
    /** Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }
    
    /** Returns a short description of the servlet.
     */
    public String getServletInfo() {
        return "Short description";
    }
    /**
     * Handle an exception.
     */
    private void handleException(String msg,Throwable t,PrintWriter out) throws ServletException, IOException {
        out.print(msg);
        out.println("<p><hr>");
        getServletContext().log(msg,t);
        throw new ServletException(msg,t);
    } 
    
}
